-- 创建数据仓库配置表
CREATE TABLE IF NOT EXISTS datawarehouse_configs (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE,
    type VARCHAR(50) NOT NULL, -- doris, clickhouse, etc.
    host VARCHAR(255) NOT NULL,
    port INTEGER NOT NULL,
    username VARCHAR(100),
    password VARCHAR(255),
    database_name VARCHAR(100),
    connection_params JSONB, -- 额外的连接参数
    is_default BOOLEAN DEFAULT FALSE,
    is_active BOOLEAN DEFAULT TRUE,
    description TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    created_by VARCHAR(100) DEFAULT 'system'
);

-- 创建更新时间的触发器
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ language 'plpgsql';

CREATE TRIGGER update_datawarehouse_configs_updated_at 
    BEFORE UPDATE ON datawarehouse_configs 
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- 插入默认的Doris配置示例
INSERT INTO datawarehouse_configs (
    name, type, host, port, username, database_name, is_default, description
) VALUES (
    'Default Doris', 'doris', 'localhost', 9030, 'root', 'dataworks_warehouse', true, '默认Doris数据仓库配置'
) ON CONFLICT (name) DO NOTHING;

-- 创建索引
CREATE INDEX IF NOT EXISTS idx_datawarehouse_configs_type ON datawarehouse_configs(type);
CREATE INDEX IF NOT EXISTS idx_datawarehouse_configs_active ON datawarehouse_configs(is_active);
CREATE INDEX IF NOT EXISTS idx_datawarehouse_configs_default ON datawarehouse_configs(is_default); 